Algorithms for merged indexes

Merged indexes are B-trees that contain multiple traditional indexes and interleave their records based on a common sort order. In relational databases, merged indexes implement “master-detail clustering” of related records, e.g., orders and order details. Thus, merged indexes shift de-normalization from the logical level of tables and rows to the physical level of indexes and records, which is a much more appropriate place for it. For object-oriented applications, clustering can reduce the I/O cost for joining rows in related tables to a fraction compared to traditional indexes, with additional beneficial effects on buffer pool requirements. Prior research has covered merged indexes without providing much guidance for their implementation. Enabling the design proposed here is a strict separation of B-tree and index into two layers of abstraction. In addition, this paper provides algorithms for (i) concurrency control and recovery including locking of individual keys and of complex objects, for (ii) data definition operations including adding and removing individual indexes in a merged index, for (iii) update operations including bulk insertions and bulk deletions, for (iv) enforcement of relational integrity constraints from uniqueness constraints to foreign key constraints, and for (v) query processing including caching in query execution plans dominated by nested iteration and index navigation. Within such a merged index, the set of tables, views, and indexes can evolve without restriction. The set of clustering columns can also evolve freely. A relational query processor can search and update index records just as in traditional indexes. With these abilities, the proposed design may finally bring general masterdetail clustering and its performance advantages to traditional databases.